<?php
require_once 'library/Utilities.php';
$util = new Utilities ( );

function json_update( $json, $type )
{
	$jsonString = str_replace ( "\\", "", $json );
	$jsonArray = json_decode ( $jsonString, true );
	$query = '';
	$queryArray = array();
	
	switch ( $type )
	{
		case 'INSERT' :
			//do insert statement
			foreach ( $jsonArray as $record )
			{
				$query = sprintf ( 'INSERT INTO %s.%s SET %s = "%s"', $record[ 'database' ], $record[ 'table' ], $record[ 'objectName' ], $record[ 'objectValue' ] );
				//echo $query;
				$queryArray[] = $query;
			}
			
			break;
		
		case 'UPDATE' :
			
			foreach ( $jsonArray as $record )
			{
				$query = sprintf ( 'UPDATE %s.%s SET %s = "%s" WHERE %s = "%s"', $record[ 'database' ], $record[ 'table' ], $record[ 'objectName' ], $record[ 'objectValue' ], $record[ 'tableKey' ], $record[ 'objectKey' ] );
				//echo $query;
				$queryArray[] = $query;
			}
			
			break;
		
		case 'DELETE' :
			
			foreach ( $jsonArray as $record )
			{
				$query = sprintf ( 'DELETE FROM %s.%s WHERE %s = "%s"', $record[ 'database' ], $record[ 'table' ], $record[ 'tableKey' ], $record[ 'objectKey' ] );
				//echo $query;
				$queryArray[] = $query;
			}
			
			break;
		
		default :
			echo 'Specify a type of statement';
			break;
	}
	return $queryArray;
}

/* ********************************************************************
 * ********************************************************************
 * 
 * 						JSON FORMAT FOR CREATING SQL
 * 
 * Below is all the methods for testing all methods from this class.
 * 
 * ********************************************************************
 * ****************************************************************** */

$flexJSONToDelete = '[{"table":"Tutorials","database":"test","tableKey":"id","objectKey":"5"},
{"table":"Tutorials","database":"test","tableKey":"id","objectKey":"6"},
{"table":"Tutorials","database":"test","tableKey":"id","objectKey":"10"}]';

$flexJSON = '[
{"table":"contacts","objectName":"name","objectValue":"Jonnie","database":"test","tableKey":"id","objectKey":"1"},
{"table":"contacts","objectName":"name","objectValue":"Sue","database":"test","tableKey":"id","objectKey":"2"},
{"table":"contacts","objectName":"name","objectValue":"Hannah","database":"test","tableKey":"id","objectKey":"3"},
{"table":"contacts","objectName":"name","objectValue":"Lauren","database":"test","tableKey":"id","objectKey":"4"},
{"table":"contacts","objectName":"name","objectValue":"John","database":"test","tableKey":"id","objectKey":"5"},
{"table":"contacts","objectName":"name","objectValue":"Rio","database":"test","tableKey":"id","objectKey":"6"},
{"table":"contacts","objectName":"name","objectValue":"Tyrrell","database":"test","tableKey":"id","objectKey":"7"}
]
';

echo '<h3>flexJSON</h3>';
echo '<pre>';
print_r ( $flexJSON );
echo '</pre>';

echo '<h3>JSON To SQL INSERT</h3>';
echo '<pre>';
print_r ( json_update ( $flexJSON, 'INSERT' ) );
echo '</pre>';

echo '<h3>JSON To SQL UPDATE</h3>';
echo '<pre>';
print_r ( json_update ( $flexJSON, 'UPDATE' ) );
echo '</pre>';

echo '<h3>JSON To SQL DELETE</h3>';
echo '<pre>';
print_r ( json_update ( $flexJSONToDelete, 'DELETE' ) );
echo '</pre>';

function batchQuery( $sql, $key )
{
	echo "<li>$key..............RUNNING MYSQLI STATEMENT... $sql</li>";
}

require_once 'library/MySQLConnect.php';

$db = new MySQLConnect ( 'localhost', 'root', 'fred' );
$db->dump ( $db->execute ( "SELECT * FROM sample.contacts" ), 'Query' );

function getStatus( $db )
{
	$res = $db->execute ( 'SHOW STATUS' );
	
	foreach ( $res as $row )
	{
		$output[ $row[ 0 ] ] = $row[ 1 ];
	}
	return $output;
}

print_r ( getStatus ( $db ) );

/**
 *  
 * Column Name
 * Column Type (for example, Integer, Char, Varchar, etc.)
 * Length or Precision
 * Scale (for decimal columns)
 * Nullability (whether or not the column accepts null)
 * Primary Key (whether or not the column is a primary key)
 * Unique (whether to add a unique constraint to the column)
 * Default Value (the default value that should be inserted when a null is attempted to be inserted into the column)
 * Auto Increment (whether the column auto-increments)
 * 
 * Example: 
 * 
 * CREATE TABLE sample.TEST_TABLE
 * (col1 INTEGER NOT NULL,
 * col2 CHAR(25),
 * col3 VARCHAR(25) NOT NULL,
 * col4 DECIMAL(10,2),
 * col5 DATE,
 * PRIMARY KEY (col1),
 * UNIQUE (col2)) ENGINE=InnoDB 
 */

$column1 = array( 
	'cName' => 'col1', 'cType' => 'int', 'cLength' => 11, 'cNull' => 'false', 'cKey' => 'true', 'cDefault' => 'Primary Key' 
);
$column2 = array( 
	'cName' => 'col2', 'cType' => 'varchar', 'cLength' => 255, 'cNull' => 'false', 'cKey' => 'false', 'cAuto' => 'false', 'cDefault' => 'Title 2' 
);
$column3 = array( 
	'cName' => 'col3', 'cType' => 'varchar', 'cLength' => 255, 'cNull' => 'false', 'cKey' => 'false', 'cAuto' => 'false', 'cDefault' => 'Title 3' 
);
$column4 = array( 
	'cName' => 'col4', 'cType' => 'varchar', 'cLength' => 255, 'cNull' => 'true', 'cKey' => 'false', 'cAuto' => 'false', 'cDefault' => 'Title 4' 
);
$columnArgsArray = array( 
	$column1, $column2, $column3, $column4 
);
$tableArgsArray = array( 
	'tName' => 'test_table', 'tEngine' => 'InnoDB', 'tColumns' => $columnArgsArray 
);
$jsonMethodArray = array( 
	'jMode' => 'create', 'jType' => 'table', 'jName' => 'test', 'jArgs' => $tableArgsArray 
);

$util->dump ( 'JSON ARGS Array', $jsonMethodArray );
$util->dump ( 'JSON ARGS JSON', json_encode ( $jsonMethodArray ) );

//$util->printNestedArray( $jsonMethodArray );


function getFields( $columnArray )
{
	foreach ( $columnArray as $column )
	{
		$query = sprintf ( '%s %s( %d ) %s DEFAULT "%s"', $column[ 'cName' ], $column[ 'cType' ], $column[ 'cLength' ], $column[ 'cNull' ], $column[ 'cDefault' ] );
	}
	echo $query;
}

if ( $jsonMethodArray[ 'jMode' ] == 'create' && $jsonMethodArray[ 'jType' ] == 'table' )
{
	$db = $jsonMethodArray[ 'jName' ];
	
	$sql = '';
	
	foreach ( $jsonMethodArray[ 'jArgs' ] as $key => $value )
	{
		print_r ( $key . "-" . $value );
		
		//check for columns array
		if ( is_array ( $value ) )
		{
			//loop through all array in columns array
			for( $i = 0; $i < count ( $value ); $i ++ )
			{
				getFields ( $value[ $i ] );
			}
		}
	}
}

?>



